package serv;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
import java.util.Vector;

import javax.servlet.jsp.JspWriter;

import sun.misc.BASE64Encoder;


/**   
 * 此类描述的是：数据库处理
 * @author: 朱凯锋   
 * @version: 2013年8月7日 上午11:24:40    
 */
public class GwkDB {
	private Connection conn = null;
	private Statement stmt=null;
	public Vector<Vector<String>> dbvt = new Vector<Vector<String>>();
	
	int connect_db() throws IOException{
		String ip = GetPrivateProfileString("HOST_IP","192.168.168.101");
		String sid = GetPrivateProfileString("SID","orcl");
		String user = GetPrivateProfileString("USER","gwk");
		String password = GetPrivateProfileString("PASSWORD","gwk");
		String url = "jdbc:oracle:thin:@" + ip + ":1521:" + sid;
		//String url = "jdbc:oracle:thin:@192.168.168.101:1521:orcl";
		//String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
		//String url = "jdbc:oracle:thin:@127.0.0.1:1521:mms";

		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			conn = DriverManager.getConnection(url, user,password);
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
		return 0;
	}
	
	
	void close_db() throws SQLException{
		if(stmt!=null){
			stmt.close();
		}
		if(!conn.isClosed()){
			conn.close();
		}
	}

	/**   
	 * 此方法描述的是： 一般查询
	 * @author: 朱凯锋   
	 * @version: 2013年8月7日 上午11:25:39
	 * @throws IOException 
	 * @return：-1,no connect;-2,sql is null;-3,no rows
	 */
	public int query(String selectsql,int col) throws SQLException, IOException{
		System.out.println("sql:"+selectsql);
		long t1 = System.currentTimeMillis();
		if (connect_db() < 0) {
			return -1;
		}
		long t2 = System.currentTimeMillis();
		System.out.println("connect time:" + (t2-t1));
		if(selectsql==null)
			return -2;

		ResultSet rs = stmt.executeQuery(convert_chn(selectsql));
		if (rs == null) {
			return -3;
		}
		t1 = System.currentTimeMillis();
		System.out.println("executeQuery time:" + (t1-t2));
		dbvt.clear();
		while (rs.next()) {
			Vector<String> vttmp = new Vector<String>();
			for(int i=1;i<=col;i++){
				if(rs.getString(i)!=null){
					vttmp.add(rs.getString(i));
				}else{
					vttmp.add("-");
				}
			}
			dbvt.add(vttmp);
		}
		close_db();
		t2 = System.currentTimeMillis();
		System.out.println("set vector time:" + (t2-t1) + " @size:" + dbvt.size());
		return 0;
	}
	/**   
	 * 此方法描述的是： 一般查询,加快效率，不close conn,but stmt.close();
	 * @author: 朱凯锋   
	 * @version: 2013年8月7日 上午11:25:39
	 * @throws IOException 
	 * @return：-1,no connect;-2,sql is null;-3,no rows
	 */
	public int query2(String selectsql,int col) throws SQLException, IOException{
		System.out.println("2sql:"+selectsql);
		long t1 = System.currentTimeMillis();
		if(conn == null||conn.isClosed()){
			if (connect_db() < 0) {
				return -1;
			}
		}
		long t2 = System.currentTimeMillis();
		System.out.println("2connect time:" + (t2-t1));
		if(selectsql==null)
			return -2;

		ResultSet rs = stmt.executeQuery(convert_chn(selectsql));
		if (rs == null) {
			return -3;
		}
		t1 = System.currentTimeMillis();
		System.out.println("2executeQuery time:" + (t1-t2));
		dbvt.clear();
		while (rs.next()) {
			Vector<String> vttmp = new Vector<String>();
			for(int i=1;i<=col;i++){
				if(rs.getString(i)!=null){
					vttmp.add(rs.getString(i));
				}else{
					vttmp.add("-");
				}
			}
			dbvt.add(vttmp);
		}
		//stmt.close();
		t2 = System.currentTimeMillis();
		System.out.println("2set vector time:" + (t2-t1) + " @size:" + dbvt.size());
		return 0;
	}
	
	/**   
	 * 此方法描述的是： 用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句
	 * @author: 朱凯锋   
	 * @version: 2013年8月22日 上午10:54:55   
	 * @return：返回更新的行数
	 */
	public int update(String othersql) throws SQLException, IOException{
		if (connect_db() < 0) {
			return -1;
		}
		if(othersql==null)
			return -2;
		System.out.println("update:"+othersql);
		//int rs = stmt.executeUpdate(convert_chn(othersql));
		int rs = stmt.executeUpdate(othersql);
		close_db();
		return rs;
	}

	static public String convert_chn(String strin){
		String strout = "";
		if(strin==null){
			return strout;
		}
		try {
			strout = new String(strin.getBytes("iso8859-1"),"gb2312");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return strout;
		
	}

	/**   
	 * 此方法描述的是：MD5加密
	 * @author: 朱凯锋   
	 * @version: 2013年8月7日 上午11:26:58   
	 */
	public static String encryptMD5(String str) throws	Exception {
		MessageDigest md5 = MessageDigest.getInstance("MD5");
		md5.update(str.getBytes());
		//return md5.digest(); 
		BASE64Encoder base64en = new BASE64Encoder(); 
		String newstr=base64en.encode(md5.digest());  
        return newstr; 
	}
	
	/**   
	 * 此方法描述的是： 取配置文件参数
	 * @author: 朱凯锋   
	 * @version: 2013年8月7日 下午3:17:44   
	 */
	public String GetPrivateProfileString(String KeyName,String Default) throws IOException{
		//System.out.println("pa5:" + this.getClass().getResource("/").getPath());
		String Profile = this.getClass().getResource("/").getPath() + "gwk.ini";
		FileInputStream fin = new FileInputStream(Profile);
		Properties prop = new Properties();
		prop.load(fin);
		fin.close();
		return prop.getProperty(KeyName, KeyName);
		//String Path = this.getClass().getResource("/").getPath();
		//return Path;
	}
	/**   
	 * 此方法描述的是： 设配置文件参数
	 * @author: 朱凯锋   
	 * @version: 2013年8月7日 下午3:17:44   
	 */
	public void SetPrivateProfileString(String KeyName,String Value) throws IOException{
		//System.out.println("pa5:" + this.getClass().getResource("/").getPath());
		String Profile = this.getClass().getResource("/").getPath() + "gwk.ini";
		FileInputStream fin = new FileInputStream(Profile);
		Properties prop = new Properties();
		prop.load(fin);
		fin.close();
		prop.setProperty(KeyName, Value);
		FileOutputStream fout = new FileOutputStream(Profile);
		prop.store(fout, "");
		fout.close();
	}

	/**
	 * 以行为单位读取文件，常用于读面向行的格式化文件
	 */
	public static void readFileByLines(String fileName,JspWriter out) {
		//判断文件是否存在
		File file = new File(fileName);
		if(!file.exists()){
			try {
				out.println("文件" + fileName + "不存在<br>");
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return;
		}
		BufferedReader reader = null;
		try {
			//System.out.println("以行为单位读取文件内容，一次读一整行：");
			//reader = new BufferedReader(new FileReader(file));
			reader = new BufferedReader(new InputStreamReader(new FileInputStream(fileName),"GBK"));
			String tempString = null;
			//int line = 1;
			// 一次读入一行，直到读入null为文件结束
			while ((tempString = reader.readLine()) != null) {
				// 显示行号
				//System.out.println("line " + line + ": " + tempString);
				out.println(tempString + "<br>");
				//out.println("line " + line + ": " + tempString + "<br>");
				//line++;
			}
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e1) {
				}
			}
		}
	}
	/**   
	 * 此方法描述的是： 调用shell脚本，输出到jsp out
	 * @author: 朱凯锋   
	 * @version: 2013年9月3日 上午10:28:38   
	 * @throws IOException 
	 */
	public static String callShell(String cmd) throws IOException {
		Process process = Runtime.getRuntime().exec(cmd);
		InputStreamReader ir = new InputStreamReader(process.getInputStream());
		LineNumberReader input = new LineNumberReader(ir);
		String ret_str = new String();
		String line;
		while ((line = input.readLine()) != null){
			//out.println(line + "<br>");
			ret_str += line + "<br />";
			System.out.println(line);
		}
		input.close();
		ir.close();
		return ret_str;
	}
	/**
	 * 得到二个日期间的间隔天数
	 * @throws ParseException 
	 * @throws IOException */
	public static void GetTransLogTwoDay(String sj1, String sj2,JspWriter out) throws ParseException, IOException{
		SimpleDateFormat myFormatter = new SimpleDateFormat("yyyyMMdd");
		//long day = 0;
		Date sdate = myFormatter.parse(sj1);
		Date edate = myFormatter.parse(sj2);
		Calendar cal=Calendar.getInstance();  
		for(cal.setTime(sdate);!cal.getTime().after(edate);cal.add(Calendar.DATE, 1)){
			String filename = "/gwcard/czimp/log/czimp." + myFormatter.format(cal.getTime()) + ".log";
			//out.println("file:" + filename + "<br>");
			readFileByLines(filename,out);
		}
		//day = (edate.getTime() - sdate.getTime()) / (24 * 60 * 60 * 1000);
		//out.println("days:" + day);
	}
}
	